package org.csu.mypetstore.persistence;

import org.apache.ibatis.annotations.Delete;
import org.apache.ibatis.annotations.Insert;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;
import org.aspectj.weaver.ast.Or;
import org.csu.mypetstore.domain.Order;
import org.springframework.stereotype.Repository;

import java.util.List;

@Repository
public interface OrderMapper {
    @Select("        SELECT\n" +
            "            * ,\n" +
            "            USERID AS username,\n" +
            "            BILLADDR1 AS billAddress1,\n" +
            "            BILLADDR2 AS billAddress2,\n" +
            "            SHIPADDR1 AS shipAddress1,\n" +
            "            SHIPADDR2 AS shipAddress2,\n" +
            "            EXPRDATE AS expiryDate\n" +
            "        from orders\n" +
            "        WHERE ORDERS.USERID = #{value}\n" +
            "        ORDER BY ORDERDATE")
    List<Order> getOrdersByUsername(String username);

    @Select("        select\n" +
            "            * ,\n" +
            "            USERID AS username,\n" +
            "            BILLADDR1 AS billAddress1,\n" +
            "            BILLADDR2 AS billAddress2,\n" +
            "            SHIPADDR1 AS shipAddress1,\n" +
            "            SHIPADDR2 AS shipAddress2,\n" +
            "            EXPRDATE AS expiryDate\n" +
            "        from orders\n" +
            "        WHERE ORDERS.ORDERID = #{value}")
    Order getOrder(int orderId);

    @Insert("INSERT INTO ORDERS (ORDERID, USERID, ORDERDATE, SHIPADDR1, SHIPADDR2, SHIPCITY, SHIPSTATE,\n" +
            "                            SHIPZIP, SHIPCOUNTRY, BILLADDR1, BILLADDR2, BILLCITY, BILLSTATE, BILLZIP, BILLCOUNTRY,\n" +
            "                            COURIER, TOTALPRICE, BILLTOFIRSTNAME, BILLTOLASTNAME, SHIPTOFIRSTNAME, SHIPTOLASTNAME,\n" +
            "                            CREDITCARD, EXPRDATE, CARDTYPE, LOCALE,STATUS)\n" +
            "        VALUES(#{orderId}, #{username}, #{orderDate}, #{shipAddress1}, #{shipAddress2,jdbcType=VARCHAR}, #{shipCity},\n" +
            "               #{shipState}, #{shipZip}, #{shipCountry}, #{billAddress1}, #{billAddress2,jdbcType=VARCHAR}, #{billCity},\n" +
            "               #{billState}, #{billZip}, #{billCountry}, #{courier}, #{totalPrice}, #{billToFirstName}, #{billToLastName},\n" +
            "               #{shipToFirstName}, #{shipToLastName}, #{creditCard}, #{expiryDate}, #{cardType}, #{locale},#{status})")
    boolean insertOrder(Order order);

    //boolean insertOrderStatus(Order order);

    @Select("        SELECT\n" +
            "            USERID AS username,\n" +
            "            ORDERS.ORDERID,\n" +
            "            TOTALPRICE,\n" +
            "            ORDERDATE,\n" +
            "            STATUS\n" +
            "        FROM ORDERS\n" +
            "        WHERE USERID = #{USERNAME}")
    List<Order> getOrdersByUserId(String userid);

    @Select("        select\n" +
            "            * ,\n" +
            "            USERID AS username,\n" +
            "            BILLADDR1 AS billAddress1,\n" +
            "            BILLADDR2 AS billAddress2,\n" +
            "            SHIPADDR1 AS shipAddress1,\n" +
            "            SHIPADDR2 AS shipAddress2,\n" +
            "            EXPRDATE AS expiryDate\n" +
            "        from orders")
    List<Order>getAllOrder();

    @Update("UPDATE ORDERS SET STATUS = '待退款' WHERE ORDERID = #{orderId}")
    boolean updateRefundOrderStatus(int orderId);

    @Delete("delete from orders where ORDERID = #{orderId}")
    boolean removeOrderByOrderId(int orderId);

    @Update("UPDATE ORDERS SET STATUS = '已退款' WHERE ORDERID = #{orderId}")
    boolean updateOverOrderStatus(int orderId);

    @Update("UPDATE ORDERS SET STATUS = '已完成' WHERE ORDERID = #{orderId}")
    boolean updateCompleteOrderStatus(int orderId);

    @Update("UPDATE ORDERS SET STATUS = '已发货' WHERE ORDERID = #{orderId}")
    boolean updateSendOrderStatus(int orderId);

    @Select("         select\n" +
            "             *,\n" +
            "            userid as username,\n" +
            "            BILLADDR1 AS billAddress1,\n" +
            "            BILLADDR2 AS billAddress2,\n" +
            "            SHIPADDR1 AS shipAddress1,\n" +
            "            SHIPADDR2 AS shipAddress2,\n" +
            "            EXPRDATE AS expiryDate\n" +
            "         from orders\n" +
            "          WHERE CAST(orderid AS CHAR) LIKE #{value}")
     List<Order> searchOrder(String orderId);

    @Update("UPDATE ORDERS SET STATUS = #{status} WHERE ORDERID = #{orderId}")
    boolean updateOrderStatus(Order order);

    @Update("        update orders set\n" +
            "           BILLADDR1=#{billAddress1},\n" +
            "           BILLADDR2=#{billAddress2},\n" +
            "           BILLCITY=#{billCity},\n" +
            "           BILLSTATE=#{billState},\n" +
            "           BILLZIP=#{billZip},\n" +
            "           BILLCOUNTRY=#{billCountry},\n" +
            "           COURIER=#{courier},\n" +
            "           BILLTOFIRSTNAME=#{billToFirstName},\n" +
            "           BILLTOLASTNAME=#{billToLastName},\n" +
            "           CREDITCARD=#{creditCard},\n" +
            "           EXPRDATE=#{expiryDate},\n" +
            "           CARDTYPE=#{cardType},\n" +
            "           LOCALE=#{locale},\n" +
            "           STATUS=#{status}\n" +
            "            where ORDERID=#{orderId}")
    boolean updateOrder(Order order);
}
